﻿using System;
using System.Collections.Generic;
using System.Data.SqlServerCe;
using System.IO;
using System.Linq;
using System.Text;

namespace Katamino.Model.Data
{
    public class SqlCEKataminoDatabase : IKataminoDatabase
    {
        private readonly string _connectionString;

        public SqlCEKataminoDatabase(string databasefile = "SqlCE_database.sdf")
        {
            var connectionString = GetConnectionString(databasefile);

            _connectionString = connectionString;
        }

        /// <summary>
        /// Create a SqlCE database for containing pentas and solutions.
        /// User is responsible for linking it to the related problem, e.g. via filename fragments.
        /// </summary>
        /// <param name="databasefile">SqlCE database file name</param>
        /// <param name="overwriteExisting">Allow overwrite of an existing database in the file.</param>
        /// <returns>Connection string to this database</returns>
        public string CreateDatabase(string databasefile = "", bool overwriteExisting = true)
        {
            string filename = String.IsNullOrEmpty(databasefile) ? GetDefaultDatabaseFilename() : databasefile;

            if (overwriteExisting && File.Exists(filename))
                File.Delete(filename);

            CreatePathToFilename(filename);

            string connStr = GetConnectionString(filename);

            SqlCeEngine engine = new SqlCeEngine(connStr);
            engine.CreateDatabase();
            engine.Dispose();

            return connStr;
        }

        private static void CreatePathToFilename(string filename)
        {
            var path = Path.GetDirectoryName(filename);
            if (Directory.Exists(path))
            {
                Console.WriteLine("That path exists already.");
                return;
            }

            // Try to create the directory.
            DirectoryInfo di = Directory.CreateDirectory(path);
            Console.WriteLine("The directory was created successfully at {0}.", Directory.GetCreationTime(path));

        }

        private static string GetDefaultDatabaseFilename()
        {
            var dir = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
            return Path.Combine(dir, "Katamino.sdf");
        }

        private static string GetConnectionString(string filename = "katamino.sdf")
        {
            return string.Format("Data Source='{0}'; LCID=1033;", filename);
        }

        public void CreateSolutionTable(string tablename = "Solution", bool overwriteExisting = true)
        {
            using (SqlCeConnection conn = GetConnection())
            {
                SqlCeCommand cmd = conn.CreateCommand();
                cmd.CommandText = GetCreateTableQuery(tablename);
                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }

        private static string GetCreateTableQuery(string tablename)
        {
            var bytecolumnstr =
                "p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,r1,r2,r3,r4,r5,r6,r7,r8,r9,r10,r11,r12,l1,l2,l3,l4,l5,l6,l7,l8,l9,l10,l11,l12";
            var bytecols = bytecolumnstr.Split(',');
            var decl = (from s in bytecols select s + " tinyint");

            return String.Format("CREATE TABLE {0} (id int, dateTimeFound datetime, ", tablename)
                   + String.Join(", ", decl)
                   + ")";
        }

        private SqlCeConnection GetConnection()
        {
            return new SqlCeConnection(_connectionString);
        }

        #region Implementation of IKataminoDatabase

        public void StorePentaDataTable(KataminoDataSet.PentaDataTable pentaDataTable)
        {
            throw new NotImplementedException();
        }

        public void StoreSolutionTable(KataminoDataSet.SolutionDataTable solutionDataTable)
        {
            throw new NotImplementedException();
        }

        public KataminoDataSet.PentaDataTable ReadPentaDataTable()
        {
            throw new NotImplementedException();
        }

        public KataminoDataSet.SolutionDataTable ReadSolutionDataTable()
        {
            throw new NotImplementedException();
        }

        #endregion
    }
}
